﻿Imports MySql.Data.MySqlClient

Public Class CGuia
    Private conn As String = My.Settings.Conexion

#Region "Get"

    ''' <summary>
    ''' Retorna los ítems de la guia que le llega
    ''' </summary>
    ''' <param name="idGuia"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetItemsGuia(ByVal idGuia As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdGuia", idGuia)
        Return Data.ExecuteDataSet(Me.conn, "GetItemsGuia", arrParam)
    End Function

    ''' <summary>
    ''' Retorna los conceptos de la guia que le llega
    ''' </summary>
    ''' <param name="idGuia"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetConceptosGuia(ByVal idGuia As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdGuia", idGuia)
        Return Data.ExecuteDataSet(Me.conn, "GetConceptosGuia", arrParam)
    End Function

    Public Function BuscarGuias(ByVal identificador As String, ByVal idCliente As Integer, ByVal desde As String, ByVal hasta As String) As DataSet
        'Construye cadena con los criterios
        Dim criterio As String = ""
        If (identificador <> "") Then
            criterio = criterio & " AND CONCAT(GUIA.IDENTIFICADOR1, ' ', GUIA.IDENTIFICADOR2, ' ',GUIA.IDENTIFICADOR3) LIKE '%" & identificador & "%'"
        End If

        If (idCliente > 0) Then
            criterio = criterio & " AND GUIA.IDCLIENTE = " & idCliente.ToString()
        End If
        If (desde.Length > 0) Then
            criterio = criterio & " AND GUIA.FECHA >= '" & desde & "'"
        End If
        If (hasta.Length > 0) Then
            criterio = criterio & " AND GUIA.FECHA <= '" & hasta & "'"
        End If        

        'Construye la consulta que se va a ejecutar
        Dim consulta As String = "SELECT GUIA.ID, CONCAT(GUIA.IDENTIFICADOR1, ' ', GUIA.IDENTIFICADOR2, ' ',GUIA.IDENTIFICADOR3) AS 'IDENTIFICADOR', "
        consulta = consulta & " CLIENTE.NOMBRE AS 'CLIENTE', GUIA.FECHA "
        consulta = consulta & " FROM GUIA INNER JOIN CLIENTE ON GUIA.IDCLIENTE = CLIENTE.ID "
        consulta = consulta & " WHERE GUIA.ACTIVO = 1 "        

        If (criterio.Length > 0) Then
            consulta = consulta & criterio
        End If

        'Agrega order by
        consulta = consulta & " ORDER BY GUIA.FECHA;"

        'Se ejecuta la consulta
        Return Data.ExecuteSelect(Me.conn, consulta, Nothing)
    End Function

    ''' <summary>
    ''' Retorna toda la info de la guia que le llega
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetGuiaById(ByVal id As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdGuia", id)
        Return Data.ExecuteDataSet(Me.conn, "GetGuiaById", arrParam)
    End Function

    ''' <summary>
    ''' Retorna la información para la imrpesión de la guía
    ''' </summary>
    ''' <param name="idGuia"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetInfoGuia(ByVal idGuia As Integer) As DataSet        
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdGuia", idGuia)
        Return Data.ExecuteDataSet(Me.conn, "GetInfoGuia", arrParam)
    End Function


    ''' <summary>
    ''' Retorna la información del reporte de guías
    ''' </summary>
    ''' <param name="idAgente"></param>
    ''' <param name="fecha1"></param>
    ''' <param name="fecha2"></param>
    ''' <param name="tipoGuia"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetReporteGuias(ByVal idAgente As Integer, ByVal fecha1 As String, ByVal fecha2 As String, ByVal tipoGuia As String, ByVal tasa As Double) As DataSet
        Dim arrParam(4) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdAgente", idAgente)
        arrParam(1) = New MySqlParameter("?varFecha1", fecha1)
        arrParam(2) = New MySqlParameter("?varFecha2", fecha2)
        arrParam(3) = New MySqlParameter("?varTipo", tipoGuia)

        'Se obtienen las guias que cumplen la condiciones y deben salir en el reporte
        Dim ds As New DataSet
        ds = Data.ExecuteDataSet(Me.conn, "GetReporteGuias", arrParam)

        'Construye dataTable de retorno
        Dim dt As New DataTable
        dt.Columns.Add("GUIANUMERO")
        dt.Columns.Add("TASA")
        dt.Columns.Add("FLETEDOLARES")
        dt.Columns.Add("DUEAGENTDOLARES")
        dt.Columns.Add("DUECARRIERDOLARES")

        'Se recorren las guías y para cada una se consultan los totales        
        Dim valorFlete As Double = 0
        Dim valorAgent As Double = 0
        Dim valorCarrier As Double = 0
        Dim dr As DataRow
        Dim dsTotales As DataSet
        Dim arrParamTotal(1) As MySqlParameter
        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
            valorFlete = 0
            valorAgent = 0
            valorCarrier = 0

            'Se obtienen los totales de la guia
            arrParamTotal(0) = New MySqlParameter("?varIdGuia", ds.Tables(0).Rows(i)("ID"))
            dsTotales = Data.ExecuteDataSet(Me.conn, "GetTotalesGuia", arrParamTotal)
            If (dsTotales.Tables(0).Rows.Count > 0) Then
                If (dsTotales.Tables(0).Rows(0)("TOTAL").ToString() <> "") Then
                    valorFlete = CDbl(dsTotales.Tables(0).Rows(0)("TOTAL").ToString())
                End If
            End If
            If (dsTotales.Tables(1).Rows.Count > 0) Then
                If (dsTotales.Tables(1).Rows(0)("TOTAL").ToString() <> "") Then
                    valorAgent = CDbl(dsTotales.Tables(1).Rows(0)("TOTAL").ToString())
                End If
            End If
            If (dsTotales.Tables(2).Rows.Count > 0) Then
                If (dsTotales.Tables(2).Rows(0)("TOTAL").ToString() <> "") Then
                    valorCarrier = CDbl(dsTotales.Tables(2).Rows(0)("TOTAL").ToString())
                End If
            End If

            dr = dt.NewRow()
            dr("GUIANUMERO") = ds.Tables(0).Rows(i)("IDENTIFICADOR")
            dr("TASA") = tasa
            dr("FLETEDOLARES") = valorFlete
            dr("DUEAGENTDOLARES") = valorAgent
            dr("DUECARRIERDOLARES") = valorCarrier

            dt.Rows.Add(dr)
        Next

        Dim dsRetorno As New DataSet
        dsRetorno.Tables.Add(dt)

        Return dsRetorno
    End Function

    ''' <summary>
    ''' Retorna dimensiones existentes para un item de guía
    ''' </summary>
    ''' <param name="idItemGuia"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDimensiones(ByVal idItemGuia As Integer) As DataSet
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdItemGuia", idItemGuia)
        Return Data.ExecuteDataSet(Me.conn, "GetDimensiones", arrParam)
    End Function

#End Region

#Region "Actualizar"

    Public Function ActualizarGuia(ByVal varIdGuia As Integer, ByVal varIdentificador1 As String, ByVal varIdentificador2 As String, ByVal varIdentificador3 As String, ByVal varEmpresaCarga As Integer, ByVal varIdAgente As Integer, ByVal varIdCliente As Integer, ByVal varInformacionCuenta As String, ByVal varIdAeropuertoOrigen As Integer, ByVal varIdAeropuertoDestino As Integer, ByVal varTo1 As Integer, ByVal varTo2 As Integer, ByVal varBy1 As Integer, ByVal varBy2 As Integer, ByVal varCHGS As String, ByVal varWTVAL As String, ByVal varOtro As String, ByVal varNumeroVuelo As String, ByVal varFecha As String, ByVal varIdCiudad As Integer, ByVal varTipoGuia As String, ByVal varAccountNumber As String, ByVal varTo3 As Integer, ByVal varBy3 As Integer, ByVal varAirWaybill As String, ByVal varHandlingInformation As String) As Integer
        Dim arrParam(27) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdGuia", varIdGuia)
        arrParam(1) = New MySqlParameter("?varIdentificador1", varIdentificador1)
        arrParam(2) = New MySqlParameter("?varIdentificador2", varIdentificador2)
        arrParam(3) = New MySqlParameter("?varIdentificador3", varIdentificador3)
        arrParam(4) = New MySqlParameter("?varEmpresaCarga", varEmpresaCarga)
        arrParam(5) = New MySqlParameter("?varIdAgente", varIdAgente)
        arrParam(6) = New MySqlParameter("?varIdCliente", varIdCliente)
        arrParam(7) = New MySqlParameter("?varInformacionCuenta", varInformacionCuenta)
        arrParam(8) = New MySqlParameter("?varIdAeropuertoOrigen", varIdAeropuertoOrigen)
        arrParam(9) = New MySqlParameter("?varIdAeropuertoDestino", varIdAeropuertoDestino)
        arrParam(10) = New MySqlParameter("?varTo1", varTo1)
        arrParam(11) = New MySqlParameter("?varTo2", varTo2)
        arrParam(12) = New MySqlParameter("?varBy1", varBy1)
        arrParam(13) = New MySqlParameter("?varBy2", varBy2)
        arrParam(14) = New MySqlParameter("?varCHGS", varCHGS)
        arrParam(15) = New MySqlParameter("?varWTVAL", varWTVAL)
        arrParam(16) = New MySqlParameter("?varOtro", varOtro)
        arrParam(17) = New MySqlParameter("?varNumeroVuelo", varNumeroVuelo)
        arrParam(18) = New MySqlParameter("?varFecha", varFecha)
        arrParam(19) = New MySqlParameter("?varIdCiudad", varIdCiudad)
        arrParam(20) = New MySqlParameter("?varTipoGuia", varTipoGuia)
        arrParam(21) = New MySqlParameter("?varAccountNumber", varAccountNumber)
        arrParam(22) = New MySqlParameter("?varTo3", varTo3)
        arrParam(23) = New MySqlParameter("?varBy3", varBy3)
        arrParam(24) = New MySqlParameter("?varAirWaybill", varAirWaybill)
        arrParam(25) = New MySqlParameter("?varHandlingInformation", varHandlingInformation)
        arrParam(26) = New MySqlParameter("?retorno", MySqlDbType.Int32)
        arrParam(26).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarGuia", arrParam)
        Return CInt(arrParam(26).Value)
    End Function

    Public Function ActualizarItemGuia(ByVal varIdItem As Integer, ByVal varIdGuia As Integer, ByVal varCantidad As Double, ByVal varPeso As Double, ByVal varUnidad As String, ByVal varPesoCarga As Double, ByVal varRata As Double, ByVal varProducto As String, ByVal varFS As Double, ByVal varFS2 As Double) As String
        Dim arrParam(11) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varIdItem", varIdItem)
        arrParam(1) = New MySqlParameter("?varIdGuia", varIdGuia)
        arrParam(2) = New MySqlParameter("?varCantidad", varCantidad)
        arrParam(3) = New MySqlParameter("?varPeso", varPeso)
        arrParam(4) = New MySqlParameter("?varUnidad", varUnidad)
        arrParam(5) = New MySqlParameter("?varPesoCarga", varPesoCarga)
        arrParam(6) = New MySqlParameter("?varRata", varRata)
        arrParam(7) = New MySqlParameter("?varProducto", varProducto)
        If (varFS = 1) Then
            arrParam(8) = New MySqlParameter("?varFS", DBNull.Value)
        Else
            arrParam(8) = New MySqlParameter("?varFS", varFS)
        End If
        If (varFS2 = 1) Then
            arrParam(9) = New MySqlParameter("?varFs2", DBNull.Value)
        Else
            arrParam(9) = New MySqlParameter("?varFs2", varFS2)
        End If
        arrParam(10) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(10).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarItemGuia", arrParam)
        Return arrParam(10).Value.ToString()
    End Function

    Public Function ActualizarConceptoGuia(ByVal varId As Integer, ByVal varIdConcepto As Integer, ByVal varIdGuia As Integer, ByVal varValor As Double, ByVal varIVA As Double, ByVal varFS2 As Double) As String
        Dim arrParam(7) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", varId)
        arrParam(1) = New MySqlParameter("?varIdConcepto", varIdConcepto)
        arrParam(2) = New MySqlParameter("?varIdGuia", varIdGuia)
        arrParam(3) = New MySqlParameter("?varValor", varValor)
        arrParam(4) = New MySqlParameter("?varIVA", varIVA)
        If (varFS2 = 0) Then
            arrParam(5) = New MySqlParameter("?varFS2", DBNull.Value)
        Else
            arrParam(5) = New MySqlParameter("?varFS2", varFS2)
        End If
        arrParam(6) = New MySqlParameter("?retorno", MySqlDbType.VarChar, 200)
        arrParam(6).Direction = ParameterDirection.Output
        Data.ExecuteNonQuery(Me.conn, "ActualizarConceptoGuia", arrParam)
        Return arrParam(6).Value.ToString()
    End Function

    Public Sub ActualizarDimension(ByVal varId As Integer, ByVal varIdItemGuia As Integer, ByVal varAlto As Double, ByVal varAncho As Double, ByVal varLargo As Double, ByVal varCantidad As Integer)
        Dim arrParam(6) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", varId)
        arrParam(1) = New MySqlParameter("?varIdItemGuia", varIdItemGuia)
        arrParam(2) = New MySqlParameter("?varAlto", varAlto)
        arrParam(3) = New MySqlParameter("?varAncho", varAncho)
        arrParam(4) = New MySqlParameter("?varLargo", varLargo)
        arrParam(5) = New MySqlParameter("?varCantidad", varCantidad)
        Data.ExecuteNonQuery(Me.conn, "ActualizarDimension", arrParam)
    End Sub

#End Region

#Region "Eliminar"

    Public Sub EliminarItemGuia(ByVal id As Integer)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", id)
        Data.ExecuteNonQuery(Me.conn, "EliminarItemGuia", arrParam)
    End Sub

    Public Sub EliminarConceptoGuia(ByVal id As Integer)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", id)
        Data.ExecuteNonQuery(Me.conn, "EliminarConceptoGuia", arrParam)
    End Sub

    Public Sub EliminarGuia(ByVal id As Integer)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", id)
        Data.ExecuteNonQuery(Me.conn, "EliminarGuia", arrParam)
    End Sub

    Public Sub EliminarDimension(ByVal id As Integer)
        Dim arrParam(1) As MySqlParameter
        arrParam(0) = New MySqlParameter("?varId", id)
        Data.ExecuteNonQuery(Me.conn, "EliminarDimension", arrParam)
    End Sub


#End Region
End Class
